﻿using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using m_pricelist._BAL_;
using Excel = Microsoft.Office.Interop.Excel;

//using Microsoft.Office.Interop.Excel;

namespace m_pricelist
{
    public partial class QuoteForm : Form
    {
        public int excelNumberCount { get; set; }
        public int NTH_ROW { get; set; }

        DataSet dsProducts = new DataSet();
        DataSet dsproductTypes = new DataSet();
        DataSet dsAllTables = new DataSet();

        DataTable serviceTable = new DataTable();
        Products BLproducts = new Products();

        public QuoteForm()
        {
            InitializeComponent();
            dsAllTables.Tables.Add("AllTables");
            dsAllTables.Tables.Add("ExcelTable");

            //If there is somting in the excel sheet put it in ExcelTable else do nothing
            if (BLproducts.isExcelEmpty() == true)
            {
                dsAllTables.Tables["ExcelTable"].Merge(BLproducts.excelTableMaker());

                foreach (DataRow row in dsAllTables.Tables["ExcelTable"].Rows)
                {
                    string currentItemNumber = row[0].ToString();
                    int units;
                    int discount;

                    if (row[2].ToString() == "")
                    {
                        discount = 0;
                    }
                    else
                    {
                        discount = Convert.ToInt32(row[2].ToString());
                    }

                    if (row[1].ToString() == "")
                    {
                        units = 0;
                    }
                    else
                    {
                         units = Convert.ToInt32(row[1].ToString());
                    }
                    
                    string strProductName = row[3].ToString();
                    string strProductTypeName = row[4].ToString();

                    if (dsProducts.Tables.Contains(strProductName) != true)
                    {
                        dsProducts.Tables.Add(strProductName);
                        DataRow curRow = dsProducts.Tables[strProductName].NewRow();
                        dsProducts.Tables[strProductName].Rows.Add(curRow);
                        dsProducts.Tables[strProductName].Merge(BLproducts.tableMaker(currentItemNumber, units, strProductTypeName, strProductName));
                        dsProducts.Tables[strProductName].Rows[0][2] = strProductName;
                        serviceTable.Merge(BLproducts.tableMaker(currentItemNumber, strProductTypeName));
                    }
                    else
                    {
                        dsProducts.Tables[strProductName].Merge(BLproducts.tableMaker(currentItemNumber, units, strProductTypeName, strProductName));
                        serviceTable.Merge(BLproducts.tableMaker(currentItemNumber, strProductTypeName));
                    }
                }
            }
        }

        private void QuoteForm_Load(object sender, EventArgs e)
        {
            try
            {
                //Initialize quote tab
                DataTable productTable = BLproducts.getProductUnits().Tables[0];
                DataRow nullRow = productTable.NewRow();
                nullRow["PRODUCT_GROUP"] = "";
                productTable.Rows.InsertAt(nullRow, 0);
                cboProductUnits.DataSource = productTable;
                cboProductUnits.DisplayMember = "PRODUCT_GROUP";

                //Initialize setup tab
                cboPricelistCurrency.SelectedIndex = 0;
                cboLanguage.SelectedIndex = 2;
                cboReseller.SelectedIndex = 0;
                cboSolutionType.SelectedIndex = 0;
                cboAgentCommision.SelectedIndex = 1;
                dbLable.Text = BLproducts.GetRegPath("DB");

                //Bakgrunnsliturinn á forminu
                this.BackColor = Color.FromArgb(0, 44, 83);
            }
            catch (Exception err)
            {
                MessageBox.Show(err.ToString());
            }
        }

        private void cboProductUnits_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                cboOptions.SelectedItem = null;
                sprOptions.DataSource = null;

                string selectedProductUnit = cboProductUnits.Text;

                if (cboProductUnits.SelectedIndex == 0)
                {
                    cboProductUnits.DataSource = BLproducts.getProductUnits().Tables[0];
                    cboProductUnits.DisplayMember = "PRODUCT_GROUP";

                    selectedProductUnit = cboProductUnits.Text;
                    Products proTypeObj = new Products();

                    cboProductType.DataSource = proTypeObj.getProductTypes(selectedProductUnit).Tables[0];
                    cboProductType.DisplayMember = "TYPE_NAME";
                }
                else
                {
                    cboProductType.DataSource = BLproducts.getProductTypes(selectedProductUnit).Tables[0];
                    cboProductType.DisplayMember = "TYPE_NAME";                  
                }
            }
            catch (Exception err)
            {
                MessageBox.Show(err.ToString());
            }
        }

        private void cboProductType_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                cboOptions.SelectedItem = null;
                sprOptions.DataSource = null;
                DataSet ds = new DataSet();
                string selectedProductType = cboProductType.Text;
                string selectedProduct;

                DataTable productTable = BLproducts.getProducts(selectedProductType).Tables[0];

                cboProducts.DataSource = productTable;     
                cboProducts.DisplayMember = "PRODUCT_NAME";
                selectedProduct = cboProducts.Text;

                ds = BLproducts.getSprItems(selectedProductType, selectedProduct);
                sprItems.DataSource = ds.Tables[0].DefaultView;
                sprItems.AutoResizeColumns();
            }

            catch (Exception err)
            {
                MessageBox.Show(err.ToString());
            }
        }

        private void cboProducts_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                cboOptions.SelectedItem = null;
                sprOptions.DataSource = null;

                string selectedProductType;
                selectedProductType = cboProductType.Text;

                DataSet ds = new DataSet();
                ds = BLproducts.getSprItems(cboProductType.Text, cboProducts.Text);

                sprItems.DataSource = ds.Tables[0].DefaultView;
                sprItems.AutoResizeColumns();
            }
            catch (Exception err)
            {
                MessageBox.Show(err.ToString());
            }
        }

        private void cboOptions_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                DataSet ds = new DataSet();

                string strItemNumber = sprItems.Rows[0].Cells[0].Value.ToString();
                string strGroupId = BLproducts.getGroupId(strItemNumber);
                ds = BLproducts.getOptAndConfigItems(cboOptions.Text, strGroupId);

                sprOptions.DataSource = ds.Tables[0].DefaultView;
                sprOptions.AutoResizeColumns();

                sprOptions.Columns[0].ReadOnly = true;
                sprOptions.Columns[1].ReadOnly = true;
                sprOptions.Columns[2].ReadOnly = true;
                sprOptions.Columns[4].ReadOnly = true;
            }
            catch (Exception err)
            {
                MessageBox.Show(err.ToString());
            }
        }

        private void sprItems_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                sprItems.Columns[0].ReadOnly = true;
                sprItems.Columns[1].ReadOnly = true;
                sprItems.Columns[2].ReadOnly = true;
                sprItems.Columns[4].ReadOnly = true;

                string strItemNumber, strGroupId;
                if (e.RowIndex >= 0)
                {
                    strItemNumber = sprItems.Rows[e.RowIndex].Cells[0].FormattedValue.ToString();
                    strGroupId = BLproducts.getGroupId(strItemNumber).ToString();
                    if (strGroupId == "0") { sprOptions.DataSource = null; }
                    cboOptions.DataSource = BLproducts.getOptAndConfGroups(strGroupId).Tables[0];
                    cboOptions.DisplayMember = "OPTIONGROUP";
                }

            }
            catch (Exception err)
            {
                MessageBox.Show(err.ToString());
            }
        }


        /// <summary>
        /// Takes the products that have been selected and puts them in the excel sheet
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void cmdIn_Click(object sender, EventArgs e)
        {    
            try
            {               
                if (BLproducts.isExcelEmpty() == true) { BLproducts.excelLineRemover(); excelNumberCount = 0; }

                NTH_ROW = (10 + excelNumberCount);
                string strProductTypeName = cboProductType.Text;
                string strProductName = cboProducts.Text;
                
                //If the product table does not exist it will be made here
                if (dsProducts.Tables.Contains(strProductName) != true)
                {
                    dsProducts.Tables.Add(strProductName.ToString());
                    DataRow curRow = dsProducts.Tables[strProductName].NewRow();
                    dsProducts.Tables[strProductName].Rows.Add(curRow);
                }

                //Reads from sprItems and puts it in a product table
                foreach (DataGridViewRow row in sprItems.Rows)
                {
                    if (row.Cells[3].Value.ToString() != "")
                    {
                        string currentItemNumber = row.Cells[0].Value.ToString();
                        int units = Convert.ToInt32(row.Cells[3].Value);
                        dsProducts.Tables[strProductName].Merge(BLproducts.tableMaker(currentItemNumber, units, strProductTypeName, strProductName));
                    }
                    row.Cells[3].Value = "";
                }

                //TODO laga ic-meat -> streamline(500w) og streamline(600w)
                //Reads from sprOptions and puts it in a product table
                foreach (DataGridViewRow item in sprOptions.Rows)
                {
                    if (item.Cells[3].Value.ToString() != "")
                    {
                        string currentItemNumber = item.Cells[0].Value.ToString();
                        int units = Convert.ToInt32(item.Cells[3].Value);
                        dsProducts.Tables[strProductName].Merge(BLproducts.tableMaker(currentItemNumber, units, strProductTypeName, strProductName));  
                    }
                    item.Cells[3].Value = "";
                }

                dsProducts.Tables[strProductName].Rows[0][2] = strProductName;

                //Takes all the tables from products and puts it in product type table
                foreach (DataTable tables in dsProducts.Tables)
                {                  
                    if (dsproductTypes.Tables.Contains(tables.Rows[1][31].ToString()) != true)
                    {
                        dsproductTypes.Tables.Add(tables.Rows[1][31].ToString());
                        dsproductTypes.Tables[tables.Rows[1][31].ToString()].Merge(tables);
                    }
                    else
                    {
                        if (dsproductTypes.Tables[tables.Rows[1][31].ToString()].ToString() != tables.Rows[1][31].ToString())
                        {
                            dsproductTypes.Tables.Add(tables.Rows[1][31].ToString());
                            dsproductTypes.Tables[tables.Rows[1][31].ToString()].Merge(tables);
                        }
                        dsproductTypes.Tables[tables.Rows[1][31].ToString()].Merge(tables);
                    }

                }

                //Takes tables from product type dataset and puts it in the all table dataset
                int rowCounter = 10;
                
                foreach (DataTable tables in dsproductTypes.Tables)
                {            
                    dsAllTables.Tables["AllTables"].Merge(tables);

                    DataRow totalRow = dsAllTables.Tables["AllTables"].NewRow();
                    dsAllTables.Tables["AllTables"].Rows.Add(totalRow);

                    //Total rows
                    totalRow[2] = "Total " + tables.TableName;
                    totalRow[6] = "=SUM(G" + (rowCounter + 1) + ":G" + (tables.Rows.Count + rowCounter - 1) + ")";
                    totalRow[8] = "=SUM(I" + (rowCounter + 1) + ":I" + (tables.Rows.Count + rowCounter - 1) + ")";
                    totalRow[11] = "=SUM(L" + (rowCounter + 1) + ":L" + (tables.Rows.Count + rowCounter - 1) + ")";
                    
                    rowCounter = tables.Rows.Count + rowCounter + 1;

                    dsproductTypes.Tables[tables.TableName].Clear();
                }

                #region excel

                Excel.Workbook workbook = Globals.ThisAddIn.Application.ActiveWorkbook;

                // creating new Excelsheet in workbook
                Microsoft.Office.Interop.Excel._Worksheet worksheet = workbook.ActiveSheet;
                
                #region Excel data table

                Globals.ThisAddIn.Application.ScreenUpdating = false;

                for (int currentRow = 0; currentRow < dsAllTables.Tables["AllTables"].Rows.Count; currentRow++)
                {
                    Products prodObj = new Products();
                    string tempString = "";

                    for (int curColumn = 0; curColumn < dsAllTables.Tables["AllTables"].Columns.Count; curColumn++)
                    {
                        if (dsAllTables.Tables["AllTables"].Rows[currentRow][curColumn].ToString() != "")
                        {
                            //get the cell value
                            tempString = dsAllTables.Tables["AllTables"].Rows[currentRow][curColumn].ToString();
                            //remove dots and dash in the string
                            tempString = prodObj.dashAndDotRemover(tempString);
                            //put the string in the excel sheet
                            worksheet.Cells[NTH_ROW + currentRow, curColumn + 1] = tempString;
                        }
                    }
                    excelNumberCount++;
                }

                #region excel loop

                
                for (int currentRow = 0; currentRow < dsAllTables.Tables["AllTables"].Rows.Count - 1; currentRow++)//Loop all the rows in the excel data talbe
                {
                    Products prodObj = new Products();

                    string str = dsAllTables.Tables["AllTables"].Rows[currentRow][11].ToString();

                    if (dsAllTables.Tables["AllTables"].Rows[currentRow][5].ToString() != "")
                    {
                    
                    //Total column
                    worksheet.Cells[currentRow + NTH_ROW , "G"] = "=E" + (currentRow + NTH_ROW ) + "*F" + (currentRow + NTH_ROW );

                    //Discount column
                    worksheet.Cells[currentRow + NTH_ROW , 8] = 0;
                   
                    //price column
                    worksheet.Cells[currentRow + NTH_ROW , "E"] = "=X" + (currentRow + NTH_ROW ) + "/(1-Y" + (currentRow + NTH_ROW ) + ")+X" + (currentRow + NTH_ROW ) + "*Z" + (currentRow + NTH_ROW ) + "+AA" + (currentRow + NTH_ROW );
                    
                    //Sub column
                    worksheet.Cells[currentRow + NTH_ROW , "I"] = "=((X" + (currentRow + NTH_ROW ) + "+AA" + (currentRow + NTH_ROW ) + "+X" + (currentRow + NTH_ROW ) + "*Z" + (currentRow + NTH_ROW ) + ")*(1-H" + (currentRow + NTH_ROW ) + ")+Y" + (currentRow + NTH_ROW ) + "*X" + (currentRow + NTH_ROW ) + "/(1-Y" + (currentRow + NTH_ROW ) + "))*F" + (currentRow + NTH_ROW );
                    
                    //Transferprice column
                    string tempString2 = dsAllTables.Tables["AllTables"].Rows[currentRow][11].ToString();
                    tempString2 = prodObj.dashAndDotRemover(tempString2);

                    if (tempString2.Contains("=") || tempString2 == "") 
                    {
                        worksheet.Cells[currentRow + NTH_ROW, "L"] = tempString2;
                    }
                    else
                    {
                        worksheet.Cells[currentRow + NTH_ROW, "L"] = "=F" + (currentRow + NTH_ROW) + "*" + tempString2;
                    }

                    //Comm% column
                    worksheet.Cells[currentRow + NTH_ROW , "J"] = "=K" + (currentRow + NTH_ROW ) + "/I" + (currentRow + NTH_ROW );

                    // Commision column
                    worksheet.Cells[currentRow + NTH_ROW , "k"] = "=I" + (currentRow + NTH_ROW ) + "-L" + (currentRow + NTH_ROW );
                        
                    //Grand Total row
                    //Total
                    worksheet.Cells[dsAllTables.Tables["AllTables"].Rows.Count + NTH_ROW + 1, "C"] = "Grand Total";
                    worksheet.Cells[dsAllTables.Tables["AllTables"].Rows.Count + NTH_ROW + 1, "G"] = prodObj.grandtotalRow(workbook, "G");

                    //sub total
                    worksheet.Cells[dsAllTables.Tables["AllTables"].Rows.Count + NTH_ROW + 1, "I"] = prodObj.grandtotalRow(workbook, "I");

                    //trasfer price total
                    worksheet.Cells[dsAllTables.Tables["AllTables"].Rows.Count + NTH_ROW + 1, "L"] = prodObj.grandtotalRow(workbook, "L");
                        
                    }
                }

                Globals.ThisAddIn.Application.ScreenUpdating = true;

                #endregion

                #endregion

                NTH_ROW++;

                dsAllTables.Tables["AllTables"].Clear();

                #endregion
                
            }
            catch (Exception err)
            {
                MessageBox.Show(err.ToString());
            }
        }

        //TODO nota clear fallið sem þorsteinn notar
        /// <summary>
        /// Clear all tables and the excel sheet
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void cmbClear_Click(object sender, EventArgs e)
        {
            Products probObj = new Products();
            cboOptions.SelectedItem = null;
            sprItems.DataSource = null;
            sprOptions.DataSource = null;
            probObj.excelLineRemover();
            excelNumberCount = 0;
            dsProducts.Clear();
        }

        /// <summary>
        /// Close the quote form
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void cmdClose_Click(object sender, EventArgs e)
        {
            QuoteForm.ActiveForm.Close();
        }

        private void marelPricelistTab_Click(object sender, EventArgs e)
        {

        }
        
        private void textSerchPricelist_TextChanged(object sender, EventArgs e)
        {
            //http://stackoverflow.com/questions/796195/c-sharp-autocomplete        
        }

        private void sprItems_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            int index = sprItems.CurrentRow.Index;
            sprItems.CurrentCell = sprItems.Rows[index].Cells[3];
        }

        private void sprOptions_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            int index = sprOptions.CurrentRow.Index;
            sprOptions.CurrentCell = sprOptions.Rows[index].Cells[3];
        }

        #region mysolution
        // Þetta er fyrir mysolution
        private void cboPSIndustry_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                cboPSIndustry.SelectedItem = null;

                string selectedIndustry = cboPSIndustry.Text;

                cboPSIndustry.DataSource = BLproducts.getIndustry().Tables[0];
                cboPSIndustry.DisplayMember = "INDUSTRY";

                cboSubIndustry.DataSource = BLproducts.getSubIndustry(selectedIndustry).Tables[0];

            }
            catch (Exception err)
            {
                MessageBox.Show(err.ToString());    
            }

        }

        private void cboSubIndustry_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {

            }
            catch (Exception err)
            {

                MessageBox.Show(err.ToString());    
            }
        }

        #endregion

        private void selectDb_Click(object sender, EventArgs e)
        {
            string filePath =  BLproducts.GetRegPath();   
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.InitialDirectory = filePath;
            DialogResult result = ofd.ShowDialog();
            string newdb = ofd.FileName;

            BLproducts.setDb(newdb);
            dbLable.Text = newdb;
        }
        
        private void cboParamUpdate_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                //TODO breyta nafninu tempstring
                string tempstr = cboParamUpdate.SelectedItem.ToString();

                sprParmUpdate.DataSource = BLproducts.GetParmUpdate(tempstr).Tables[0];                                
                sprParmUpdate.AutoResizeColumns();
            }
            catch (Exception err)
            {
                MessageBox.Show(err.ToString());
            }
        }

        private void sprParmUpdate_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        
        private void cboPricelistCurrency_SelectedIndexChanged(object sender, EventArgs e)
        {
            string cboCurrency = "Currency";
            string currency = cboPricelistCurrency.Text;

            BLproducts.updateDB_basic(currency,cboCurrency);
        }

        private void cboReseller_SelectedIndexChanged(object sender, EventArgs e)
        {
            string sCboReseller = "Reseller";
            string reseller = cboReseller.Text;

            if (cboReseller.SelectedItem.ToString() == "Agent")
            {
                lblAgentCommision.Visible = true;
                cboAgentCommision.Visible = true;
            }

            if (cboReseller.SelectedItem.ToString() == "SSU")
            {
                lblAgentCommision.Visible = false;
                cboAgentCommision.Visible = false;
            }

            BLproducts.updateDB_basic(reseller,sCboReseller);
        }

    }
}